package com.qulai.amazon_us.mapper.basic;

import com.qulai.amazon_us.domain.basic.Product;
import com.qulai.amazon_us.domain.basic.dto.ProductDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 商品库存Mapper接口
 *
 * @author hunan
 * @date 2025-01-03
 */
public interface ProductMapper {
    /**
     * 查询商品库存
     *
     * @param id 商品库存主键
     * @return 商品库存
     */
    public Product selectProductById(String id);

    public Product selectProductBySku(String sku);

    /**
     * 查询商品库存列表
     *
     * @param product 商品库存
     * @return 商品库存集合
     */
    public List<Product> selectProductList(ProductDto product);

    /**
     * 新增商品库存
     *
     * @param product 商品库存
     * @return 结果
     */
    public int insertProduct(Product product);

    /**
     * 修改商品库存
     *
     * @param product 商品库存
     * @return 结果
     */
    public int updateProduct(Product product);

    /**
     * 删除商品库存
     *
     * @param id 商品库存主键
     * @return 结果
     */
    public int deleteProductById(String id);

    /**
     * 批量删除商品库存
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteProductByIds(String[] ids);


    @Select("update basic_product set status = #{status}, remark = #{remark} ,update_time = NOW() where ${whereSql}")
    public void updateOfflineProduct(@Param("whereSql") String whereSql, @Param("status") Integer status, @Param("remark") String remark);


    @Update("update basic_product set status = #{status},update_by = #{optName}, update_time = NOW() where id in (${basicIds})")
    public int batchUpdateStatus(@Param("basicIds") String basicIds, @Param("status") Integer status, @Param("optName")String optName);

    /**
     * 分页查询商品列表
     *
     * @param platform 平台
     * @param offset 偏移量
     * @param pageSize 每页大小
     * @return 商品列表
     */
    @Select("SELECT id, published, sku, price, quantity FROM basic_product WHERE platform = #{platform} ORDER BY id DESC LIMIT #{offset}, #{pageSize}")
    List<Product> listProductsByPlatformPaged(@Param("platform") String platform, @Param("offset") int offset, @Param("pageSize") int pageSize);
    
    /**
     * 根据ID列表查询商品信息
     *
     * @param basicIds 商品ID列表
     * @return 商品列表
     */
    @Select("SELECT sku, price, ship_fee, quantity, published, status, class_name, extra_field FROM basic_product WHERE id IN (${basicIds})")
    List<Product> selectBasicProductsByIds(@Param("basicIds") String basicIds);
    
    /**
     * 批量更新商品信息
     *
     * @param products 商品列表
     * @return 结果
     */
    @Update("<script>" +
            "UPDATE basic_product " +
            "SET published = CASE " +
            "<foreach collection='products' item='product' separator=' '>" +
            "    WHEN id = #{product.id} THEN #{product.published} " +
            "</foreach>" +
            "END, " +
            "quantity = CASE " +
            "<foreach collection='products' item='product' separator=' '>" +
            "    WHEN id = #{product.id} THEN #{product.quantity} " +
            "</foreach>" +
            "END, " +
            "price = CASE " +
            "<foreach collection='products' item='product' separator=' '>" +
            "    WHEN id = #{product.id} THEN #{product.price} " +
            "</foreach>" +
            "END, " +
            "update_time = NOW() " +
            "WHERE id IN " +
            "<foreach collection='products' item='product' open='(' separator=',' close=')'>" +
            "    #{product.id} " +
            "</foreach>" +
            "</script>")
    int batchUpdateProducts(@Param("products") List<Product> products);
    
    /**
     * 根据平台查询所有商品
     *
     * @param platform 平台
     * @return 商品列表
     */
    @Select("SELECT id, published, sku, price, quantity FROM basic_product WHERE platform = #{platform} ORDER BY id DESC")
    List<Product> selectProductsByPlatform(@Param("platform") String platform);


    @Select({
            "<script>",
            "SELECT COUNT(bp.id)",
            "FROM basic_product bp",
            "<if test='basicProductIndex != null and basicProductIndex > 1'>",
            " INNER JOIN basic_image_to_sku bs ON bs.base_product_id = bp.id and bs.image_index = #{basicProductIndex} ",
            "</if>",
            " WHERE NOT EXISTS (SELECT 1 FROM ${tables} r WHERE r.basic_id = bp.id AND r.times = #{times} AND r.brand = #{brand} AND r.image_index = #{basicProductIndex} )",
            "<if test='basicPlatformList != null and basicPlatformList.size() > 0'>",
            "    AND bp.platform IN",
            "    <foreach collection='basicPlatformList' item='platform' open='(' separator=',' close=')'>",
            "        #{platform}",
            "    </foreach>",
            "</if>",
            "AND bp.class_name NOT IN",
            "  <choose>",
            "    <when test='notClassNameList != null and notClassNameList.size() > 0'>",
            "      <foreach collection='notClassNameList' item='className' open='(' separator=',' close=')'>",
            "        #{className}",
            "      </foreach>",
            "    </when>",
            "    <otherwise>('')</otherwise>",  // 确保语法正确
            "  </choose>",
            "AND bp.product_type !='' and bp.product_type NOT IN ('WALL_ART','wallart')",
            "AND bp.quantity > 3",
            "AND bp.not_available NOT LIKE '%amazon%'",
            "AND bp.status = 1",
            "AND bp.published = 1",
            "AND bp.price > 1",
            "AND bp.image_count >= 3",
            "</script>"
    })
    int selectProductRestNum(
            @Param("brand") String brand,
            @Param("notClassNameList") List<String> notClassNameList, // 改为List类型
            @Param("times") Integer times,
            @Param("tables") String tables,
            @Param("basicPlatformList") List<String> basicPlatformList,
            @Param("basicProductIndex") String basicProductIndex
    );

}
